package org.alex.samples.dao;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.alex.samples.dao.ISubjectDao;
import org.alex.samples.model.ExamScore;
import org.alex.samples.model.Subject;
import org.alex.samples.model.SubjectVO;
import org.alex.samples.model.UserSubject;
import org.hibernate.type.StringType;
import org.springframework.beans.BeanUtils;
import org.springframework.stereotype.Repository;


@Repository
public class ISubjectDaoImpl extends BaseHibernateDAO<Subject> implements ISubjectDao {

	public List<Subject> findAllSubject() throws Exception {
		return loadAll();
	}

	public Subject findSubjectById(Integer id) throws Exception {
		return  get(id);
		
	}

	public void createSubject(Subject subject) throws Exception {
		save(subject);
	}

	public void EditSubject(Subject subject) throws Exception {
		update(subject);
	}

	public void deleteSubject(Integer id) throws Exception {
		deleteByKey(id);
	}

	@Override
	public Iterator iterate(String queryString, Object[] values) {
		return null;
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getAllUserBySubject(Integer subjectId) throws Exception {
		StringBuilder sql=new StringBuilder();
		sql.append("select user.user_id as userId ");
		sql.append(" from exam_user_subject eus ");
		sql.append(" left join exam_subject es on eus.sub_id = es.sub_id ");
		sql.append(" left join exam_user user on user.user_id = eus.user_id ");
		sql.append(" where es.sub_id= :subjectId ");
		return this.createSqlQuery(String.valueOf(sql)).addScalar("userId",StringType.INSTANCE).setInteger("subjectId", subjectId).list();
		
	}

	@Override
	public void inserSubjectStudent(List<String> inserList, Integer subjcetId) throws Exception {	
		String hql="insert into exam_user_subject (user_id,sub_id) values ('"+inserList.get(0)+"',"+subjcetId+")";
		for(int i = 1;i<inserList.size();i++){
			hql+=",('"+inserList.get(i)+"',"+subjcetId+")";
		}
		System.out.println(hql);
		this.createSqlQuery(hql).executeUpdate();
	}

	@Override
	public void delSubjectStudent(List<String> delList, Integer subjcetId) throws Exception {
		for(int i = 0;i<delList.size();i++){
			String hql="delete from UserSubject where userId='"+delList.get(i)+"' and subId="+subjcetId ;
			System.out.println(hql);
			this.createQuery(hql).executeUpdate();
		}
		
	}

	@Override
	public void changeStatusById(Integer id, Integer status) throws Exception {
		String hql="update Subject set status="+status+" where id="+id;
		this.createQuery(hql).executeUpdate();
	}
	
	@Override
	public void flush() {
		flush();

	}
}